﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.Linq;
using MethodWorx.CMS.Requests;
using MethodWorx.Core.DomainModel;

namespace MethodWorx.CMS.Services
{
    public class PhysicalDatabaseService
    {
        private DataContext context;
        private UserService users;
        private IUnitOfWorkFactory factory;

        public PhysicalDatabaseService(DataContext context, UserService users, IUnitOfWorkFactory uowFactory)
        {
            this.context = context;
            this.users = users;
            this.factory = uowFactory;
        }

        public void ClearDatabase()
        {
            var sqlConnection = context.Connection;
            //
            //  ok, open the connection
            if(sqlConnection.State == ConnectionState.Closed)
                sqlConnection.Open();

            //  and execute the script
            var command = sqlConnection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT name FROM sysobjects WHERE type = 'U'";

            List<string> tables = new List<string>();

            using(var reader = command.ExecuteReader())
            {
                while(reader.Read())
                {
                   tables.Add(reader.GetString(0));
                }
            }

            int offset = 0;
            while(tables.Any())
            {
                var table = tables[offset];
                try
                {
                    var deleteCommand = sqlConnection.CreateCommand();
                    deleteCommand.CommandType = CommandType.Text;
                    deleteCommand.CommandText = "DELETE FROM [" + table + "]";
                    deleteCommand.ExecuteNonQuery();

                    tables.RemoveAt(offset);
                    offset = 0;
                }
                catch (Exception)
                {
                    offset++;
                    if (offset >= tables.Count)
                        throw;
                }
            }

            this.CreateDefaultUsers();

        }

        private void CreateDefaultUsers()
        {
            users.CreateDefaultUsers(this.factory);
        }
    }
}
